-- 创建停车场管理系统数据库
USE master;
GO

-- 如果数据库已存在，先删除
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ParkingManagement')
BEGIN
    ALTER DATABASE ParkingManagement SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ParkingManagement;
END
GO

-- 创建新数据库
CREATE DATABASE ParkingManagement;
GO

USE ParkingManagement;
GO

-- 创建车辆进出记录表
CREATE TABLE VehicleEntryExit (
    RecordID INT IDENTITY(1,1) PRIMARY KEY,
    ParkingLotID INT NOT NULL,
    VehicleNumber NVARCHAR(20) NOT NULL,
    EntryTime DATETIME,
    ExitTime DATETIME NULL,
    ParkingDuration AS CASE
                         WHEN ExitTime IS NULL THEN NULL
                         ELSE DATEDIFF(MINUTE, EntryTime, ExitTime)
                       END,
    PaymentAmount DECIMAL(10, 2) NULL,
    PaymentStatus NVARCHAR(20) NULL,
    GateEntryID INT NOT NULL,
    GateExitID INT NULL,
    VehicleType NVARCHAR(20) NOT NULL
);

-- 创建停车场信息表
CREATE TABLE ParkingLot (
    ParkingLotID INT IDENTITY(1,1) PRIMARY KEY,
    ParkingLotName NVARCHAR(100) NOT NULL,
    ParkingLotType NVARCHAR(50) NOT NULL,  -- 停车场、风景区、医院、学校等
    TotalParkingSpaces INT NOT NULL,
    Address NVARCHAR(200),
    ContactPhone NVARCHAR(20),
    CreatedTime DATETIME DEFAULT GETDATE()
);

-- 创建收费标准表
CREATE TABLE PricingRules (
    RuleID INT IDENTITY(1,1) PRIMARY KEY,
    ParkingLotID INT NOT NULL,
    VehicleType NVARCHAR(20) NOT NULL,
    BasePricePerHour DECIMAL(10, 2) NOT NULL,
    MaximumDailyFee DECIMAL(10, 2) NOT NULL,
    SpecialRules NVARCHAR(500) NULL,
    EffectiveFrom DATETIME NOT NULL,
    EffectiveTo DATETIME NULL,
    FOREIGN KEY (ParkingLotID) REFERENCES ParkingLot(ParkingLotID)
);

-- 创建会员信息表
CREATE TABLE Members (
    MemberID INT IDENTITY(1,1) PRIMARY KEY,
    VehicleNumber NVARCHAR(20) NOT NULL,
    OwnerName NVARCHAR(50) NOT NULL,
    MembershipType NVARCHAR(20) NOT NULL,
    RegistrationDate DATETIME DEFAULT GETDATE(),
    ExpiryDate DATETIME NOT NULL,
    ContactPhone NVARCHAR(20),
    Balance DECIMAL(10, 2) DEFAULT 0.00,
    ParkingLotID INT NOT NULL,
    FOREIGN KEY (ParkingLotID) REFERENCES ParkingLot(ParkingLotID)
);

-- 创建道闸设备信息表
CREATE TABLE GateDevice (
    GateID INT IDENTITY(1,1) PRIMARY KEY,
    ParkingLotID INT NOT NULL,
    GateType NVARCHAR(20) NOT NULL,  -- Entry, Exit, Both
    GateLocation NVARCHAR(100) NOT NULL,
    LastMaintenanceDate DATETIME NULL,
    InstallationDate DATETIME NOT NULL,
    DeviceStatus NVARCHAR(20) DEFAULT 'Active',
    FOREIGN KEY (ParkingLotID) REFERENCES ParkingLot(ParkingLotID)
);

-- 创建设备故障记录表
CREATE TABLE DeviceErrors (
    ErrorID INT IDENTITY(1,1) PRIMARY KEY,
    GateID INT NOT NULL,
    ErrorTime DATETIME NOT NULL,
    ErrorDescription NVARCHAR(500) NOT NULL,
    ResolutionTime DATETIME NULL,
    Resolution NVARCHAR(500) NULL,
    FOREIGN KEY (GateID) REFERENCES GateDevice(GateID)
);

-- 创建索引以提高查询性能
CREATE INDEX IX_VehicleEntryExit_VehicleNumber ON VehicleEntryExit(VehicleNumber);
CREATE INDEX IX_VehicleEntryExit_EntryTime ON VehicleEntryExit(EntryTime);
CREATE INDEX IX_VehicleEntryExit_ExitTime ON VehicleEntryExit(ExitTime);
CREATE INDEX IX_Members_VehicleNumber ON Members(VehicleNumber);

GO 